跳到主要内容

MySQL 的优化思路

这里列举一些优化 MySQL 查询性能的方法。以后遇到有性能问题的时候，可以根据这些方法来进行排查。

SQL 层面的优化​

  • 避免使用通配符(*)，只选择需要的列。
  • 使用具体的列名而不是 SELECT *。
  • 避免在查询中使用不必要的子查询。
  • 使用 JOIN 操作代替多个单独的查询。
  • 注意 WHERE 条件的顺序，将限制条件放在前面，以便尽早过滤不必要的记录。

为啥需要避免使用通配符(*)？​

避免使用通配符(*)是为了提高查询的效率和可读性。以下是一些原因：

  1. 查询性能：使用通配符()会选择所有的列，包括不需要的列。这可能导致数据库读取和传输大量不必要的数据，增加了网络传输和 IO 操作的开销。而且，当表结构发生变化时，查询中的通配符()可能会导致意外的结果集变化。

  2. 网络传输和存储开销：查询返回的数据量越大，网络传输和存储的开销就越大。通过只选择需要的列，可以减少返回结果的数据量，提高网络传输和存储的效率。

  3. 缓存效果：数据库的查询结果通常会被缓存，以便后续的查询能够更快地获取结果。当使用通配符(*)时，查询结果的结构可能会发生变化，导致缓存无法有效利用。而且，由于缓存的大小有限，返回大量不必要的数据会浪费缓存空间，降低缓存的效果。（这个缓存在 8.0 被干掉了）

  4. 可读性和维护性：明确指定需要的列可以使查询语句更加清晰和易读。当其他人阅读或维护代码时，可以更容易理解查询的意图和结果。

虽然在某些情况下，使用通配符()可能会更加方便，但从性能和可读性的角度考虑，建议避免使用通配符()，而是显式列出需要的列。这样可以提高查询性能，减少网络传输和存储开销，并使代码更易于理解和维护。

WHERE 条件顺序带来的影响​

在编写 SQL 查询语句时，将最具限制性的条件放在 WHERE 子句的前面，以尽早地过滤出不符合条件的记录，减少后续的处理和比较操作，从而提高查询性能。

举一个具体的例子来说明这个概念：

假设有一个名为 users 的表，包含以下字段：id、name、age、city 和 gender。现在需要查询年龄大于 30 岁、居住在纽约（city='New York'）并且性别为女性（gender='female'）的用户信息。

不注意 WHERE 条件的顺序，查询语句可能如下所示：

SELECT id, name, age, city, gender
FROM users
WHERE city = 'New York' AND age > 30 AND gender = 'female';

这种情况下，首先判断 city 是否为'New York'，然后再判断 age 是否大于30，最后判断 gender 是否为'female'。如果表中的记录很多，而符合条件的记录较少，那么在判断 city 和 age 时就会对所有的记录进行比较，浪费了一部分资源。

反之，如果调整 WHERE 条件的顺序，将限制条件放在前面，如下所示：

SELECT id, name, age, city, gender
FROM users
WHERE age > 30 AND city = 'New York' AND gender = 'female';

这样，首先判断 age 是否大于30，只有满足这个条件的记录才会进行后续的比较判断，可以尽早地过滤掉不符合条件的记录，减少了后续的比较操作，提高了查询性能。

通过调整 WHERE 条件的顺序，将最具限制性的条件放在前面，可以尽早地过滤掉不必要的记录，缩小查询范围，减少数据库的工作量，从而提高查询性能。在实际应用中，根据具体的查询条件和数据分布情况，选择合适的条件顺序进行优化。

使用索引​

  • 确保表中的常用查询字段有适当的索引。
  • 避免在索引列上进行函数操作，以充分利用索引。
  • 了解不同类型的索引（B树索引、哈希索引等）的适用场景和特性。

为啥避免在索引列上函数操作​

避免在索引列上进行函数操作是为了优化查询性能和索引的有效利用。以下是一些原因：

  1. 索引的使用：当对索引列应用函数操作时，数据库无法直接使用索引来加速查询。索引的目的是提高查询的速度，通过索引可以快速定位满足条件的记录。然而，如果在索引列上应用函数操作，数据库需要先对索引列上的每个值进行函数操作，然后才能进行匹配和筛选，这会导致索引失效，无法有效利用索引的优势。

  2. 函数计算的开销：函数操作可能会引入计算开销，尤其是在大数据量的情况下。当函数操作应用于索引列时，数据库需要对每个索引值进行计算，这可能会导致额外的 CPU 和内存开销，影响查询的性能。

  3. 查询优化器的限制：数据库查询优化器可能无法有效处理在索引列上进行函数操作的查询。优化器负责选择最佳的查询执行计划，以提高查询的性能。然而，当函数操作涉及到索引列时，优化器可能无法正确估计函数操作的成本和选择最佳的执行计划，导致查询性能下降。

为了充分利用索引并提高查询性能，建议尽量避免在索引列上进行函数操作。如果需要应用函数操作，可以考虑对索引列进行预处理或使用其他优化策略，例如引入虚拟列或使用索引辅助表。通过优化查询语句和索引设计，尽量减少函数操作的使用，可以提高查询的效率和性能。

分析和优化查询计划​

  • 使用 EXPLAIN 语句来分析查询计划，查看索引使用情况和执行计划。
  • 根据 EXPLAIN 的结果优化查询，确保使用最佳的索引和 JOIN 方式。
  • 使用索引提示（Index Hints）来强制使用特定的索引。

索引提示是什么？​

索引提示（Index Hints）是一种在 SQL 语句中指定使用特定索引的机制。它可以用来告诉数据库查询优化器在执行查询时使用指定的索引，而不是依赖于优化器自动选择索引。

索引提示通常以特定的注释语法添加到查询语句中，以指示数据库使用特定的索引。不同的数据库系统可能会有不同的语法和方式来提供索引提示。以下是一个示例，展示了在 MySQL 中使用索引提示的方式：

SELECT /*+ INDEX(index_name) */ column1, column2
FROM table_name
WHERE condition;

在这个示例中，INDEX(index_name) 是一个注释，其中 index_name 是所需索引的名称。通过将索引提示添加到查询语句中，我们可以告诉 MySQL 使用指定的索引来执行查询，而不是根据优化器的选择。

索引提示的使用应该谨慎，并且仅在特定情况下考虑。它通常用于解决查询优化器无法选择最佳索引的情况，或者在特定场景下需要强制使用特定索引的情况。然而，使用索引提示可能会导致查询性能下降或不稳定，因为它绕过了优化器的智能选择。因此，应该在深入了解查询和索引优化的基础上谨慎使用索引提示，并进行性能测试和评估来确定是否确实需要使用索引提示。

调整服务器参数​

  • 适当调整服务器的缓冲区大小，如查询缓存、连接池等。
  • 调整并发连接数、线程池大小等参数以适应实际负载。

数据库架构优化​

  • 垂直拆分和水平拆分数据表，将大表拆分成多个小表，减少单个查询的数据量。
  • 合理设计数据库表结构，避免冗余字段和无效数据。

拆库和拆表​

拆库和拆表是常用的数据库架构优化手段，可以有效提高数据库的性能和可用性。拆库和拆表的目的是将单个数据库或数据表拆分成多个数据库或数据表，以减少单个数据库或数据表的负载，提高数据库的性能和可用性。

但是它也引入了更多的复杂性，例如跨库事务、跨库 JOIN 等问题，需要在应用层进行处理。因此，拆库和拆表应该谨慎使用，仅在必要的情况下使用。

例如分布式事务，分布式事务是指事务的参与者分布在不同的节点上，而这些节点可能属于不同的数据库。在分布式事务中，事务的参与者可能需要跨库进行事务操作，这就需要在应用层实现跨库事务的处理。（在 Golang 中可以使用 DTM 工具去帮我们完成）

使用缓存​

缓存是一种常用的数据库架构优化手段，可以有效提高数据库的性能和可用性。缓存的目的是将数据缓存在内存中，以减少对数据库的访问，提高数据库的性能和可用性。

但是它也引入了更多的复杂性，例如缓存一致性、缓存更新等问题，需要在应用层进行处理。因此，缓存应该谨慎使用，仅在必要的情况下使用。

定期维护和优化数据库​

  • 定期执行表优化操作（如OPTIMIZE TABLE）以优化表结构和索引。
  • 定期清理无效数据和日志文件。